package com.dao;

import com.model.Assignment;
import com.utils.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class AssignmentDao {
    
    public List<Assignment> getAllAssignments() throws SQLException {
        String sql = "SELECT a.*, t.tname FROM Assignment a " +
                    "LEFT JOIN Teacher t ON a.tno = t.tno " +
                    "ORDER BY a.publishtime DESC";
        List<Assignment> assignments = new ArrayList<>();
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                Assignment assignment = new Assignment();
                assignment.setAid(rs.getInt("aid"));
                assignment.setTitle(rs.getString("title"));
                assignment.setContent(rs.getString("content"));
                assignment.setPublishTime(rs.getTimestamp("publishtime"));
                assignment.setDeadline(rs.getTimestamp("deadline"));
                assignment.setStatus(rs.getString("status"));
                assignment.setTno(rs.getInt("tno"));
                assignments.add(assignment);
            }
        }
        return assignments;
    }
    
    public void addAssignment(Assignment assignment) throws SQLException {
        String sql = "INSERT INTO Assignment (title, content, publishtime, deadline, status, is_visible) " +
                    "VALUES (?, ?, ?, ?, ?, true)";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, assignment.getTitle());
            pstmt.setString(2, assignment.getContent());
            pstmt.setTimestamp(3, new Timestamp(assignment.getPublishTime().getTime()));
            pstmt.setTimestamp(4, new Timestamp(assignment.getDeadline().getTime()));
            pstmt.setString(5, assignment.getStatus());
            
            pstmt.executeUpdate();
        }
    }
    
    public void deleteAssignment(int aid) throws SQLException {
        String sql = "DELETE FROM Assignment WHERE aid = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, aid);
            pstmt.executeUpdate();
        }
    }
    
    public List<Map<String, Object>> getAssignmentSubmissions(int aid) throws SQLException {
        String sql = "SELECT s.submission_id, s.sno, st.Sname, s.content, s.submittime, s.grade, s.comment " +
                    "FROM Assignment_Submission s " +
                    "JOIN Student st ON s.sno = st.Sno " +
                    "WHERE s.aid = ? " +
                    "ORDER BY s.submittime DESC";
                    
        List<Map<String, Object>> submissions = new ArrayList<>();
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, aid);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Map<String, Object> submission = new HashMap<>();
                submission.put("submissionId", rs.getInt("submission_id"));
                submission.put("studentNo", rs.getInt("sno"));
                submission.put("studentName", rs.getString("Sname"));
                submission.put("content", rs.getString("content"));
                submission.put("submitTime", rs.getTimestamp("submittime"));
                submission.put("grade", rs.getInt("grade"));
                submission.put("comment", rs.getString("comment"));
                submissions.add(submission);
            }
        }
        return submissions;
    }
    
    public void gradeSubmission(int submissionId, int grade, String comment) throws SQLException {
        String sql = "UPDATE Assignment_Submission SET grade = ?, comment = ? WHERE submission_id = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, grade);
            pstmt.setString(2, comment);
            pstmt.setInt(3, submissionId);
            pstmt.executeUpdate();
        }
    }
    
    public Map<String, Object> getAssignmentStats(int aid) throws SQLException {
        String sql = "SELECT " +
                    "COUNT(*) as total_submissions, " +
                    "AVG(grade) as avg_grade, " +
                    "MAX(grade) as max_grade, " +
                    "MIN(grade) as min_grade, " +
                    "SUM(CASE WHEN grade IS NOT NULL THEN 1 ELSE 0 END) as graded_count " +
                    "FROM Assignment_Submission WHERE aid = ?";
        
        Map<String, Object> stats = new HashMap<>();
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, aid);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                stats.put("totalSubmissions", rs.getInt("total_submissions"));
                stats.put("averageGrade", rs.getDouble("avg_grade"));
                stats.put("maxGrade", rs.getInt("max_grade"));
                stats.put("minGrade", rs.getInt("min_grade"));
                stats.put("gradedCount", rs.getInt("graded_count"));
            }
        }
        return stats;
    }
    
    public int[] getGradeDistribution(int aid) throws SQLException {
        // 初始化5个分数段的计数：[0-60, 60-70, 70-80, 80-90, 90-100]
        int[] distribution = new int[5];
        
        String sql = "SELECT grade FROM Assignment_Submission WHERE aid = ? AND grade IS NOT NULL";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, aid);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                int grade = rs.getInt("grade");
                if (grade < 60) {
                    distribution[0]++;
                } else if (grade < 70) {
                    distribution[1]++;
                } else if (grade < 80) {
                    distribution[2]++;
                } else if (grade < 90) {
                    distribution[3]++;
                } else {
                    distribution[4]++;
                }
            }
        }
        return distribution;
    }
    
    public Assignment getAssignment(int aid) throws SQLException {
        String sql = "SELECT * FROM Assignment WHERE aid = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, aid);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Assignment assignment = new Assignment();
                    assignment.setAid(rs.getInt("aid"));
                    assignment.setTitle(rs.getString("title"));
                    assignment.setContent(rs.getString("content"));
                    assignment.setPublishTime(rs.getTimestamp("publishTime"));
                    assignment.setDeadline(rs.getTimestamp("deadline"));
                    assignment.setStatus(rs.getString("status"));
                    assignment.setTno(rs.getInt("tno"));
                    return assignment;
                }
            }
        }
        return null;
    }
    
    public void updateAssignment(Assignment assignment) throws SQLException {
        String sql = "UPDATE Assignment SET title = ?, content = ?, deadline = ? WHERE aid = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, assignment.getTitle());
            pstmt.setString(2, assignment.getContent());
            pstmt.setTimestamp(3, new Timestamp(assignment.getDeadline().getTime()));
            pstmt.setInt(4, assignment.getAid());
            
            pstmt.executeUpdate();
        }
    }
    
    public List<Assignment> getActiveAssignments() throws SQLException {
        String sql = "SELECT * FROM Assignment " +
                    "WHERE is_visible = true " +
                    "AND (deadline > NOW() OR deadline IS NULL) " +
                    "ORDER BY publishtime DESC";
        List<Assignment> assignments = new ArrayList<>();
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                Assignment assignment = new Assignment();
                assignment.setAid(rs.getInt("aid"));
                assignment.setTitle(rs.getString("title"));
                assignment.setContent(rs.getString("content"));
                assignment.setPublishTime(rs.getTimestamp("publishtime"));
                assignment.setDeadline(rs.getTimestamp("deadline"));
                assignment.setStatus(rs.getString("status"));
                assignments.add(assignment);
            }
        }
        return assignments;
    }
    
    public Map<String, Object> getStudentSubmission(int aid, int sno) throws SQLException {
        String sql = "SELECT * FROM Assignment_Submission WHERE aid = ? AND sno = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, aid);
            pstmt.setInt(2, sno);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Map<String, Object> submission = new HashMap<>();
                    submission.put("submissionId", rs.getInt("submission_id"));
                    submission.put("content", rs.getString("content"));
                    submission.put("submitTime", rs.getTimestamp("submitTime"));
                    submission.put("grade", rs.getInt("grade"));
                    submission.put("comment", rs.getString("comment"));
                    return submission;
                }
            }
        }
        return null;
    }
    
    public void submitAssignment(int aid, int sno, String content, java.sql.Timestamp submitTime) throws SQLException {
        System.out.println("Submitting assignment - AID: " + aid + ", SNO: " + sno);
        
        String sql = "INSERT INTO Assignment_Submission (aid, sno, content, submittime) VALUES (?, ?, ?, ?) " +
                    "ON DUPLICATE KEY UPDATE content = ?, submittime = ?";
        
        try (Connection conn = JdbcUtils.getDs().getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, aid);
            pstmt.setInt(2, sno);
            pstmt.setString(3, content);
            pstmt.setTimestamp(4, submitTime);
            pstmt.setString(5, content);
            pstmt.setTimestamp(6, submitTime);
            
            int rows = pstmt.executeUpdate();
            System.out.println("Affected rows: " + rows);
        }
    }
} 